////////////////////////////////////////////////////////////////////////////////
// Copyright 2007 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   CATALOG_SECURITY
// AUTHOR           :   Saint Peng 
// DATE CREATE      :   2007/03/29
// DESCRIPTION      :   a COMMON DAO file for the function, CATALOG_SECURITY
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2007/03/29   Saint Peng      1.0                 source is generated.
//
//
//
////////////////////////////////////////////////////////////////////////////////

package com.erp.app.catalog.catalogSecurity.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.catalog.catalogSecurity.dao.CatalogSecurityCommonDao;
import com.erp.app.catalog.catalogSecurity.vo.CatalogSecurityHdrVo;
import com.erp.app.catalog.catalogSecurity.bizLogic.CatalogSecurityCommonUtil;

import com.erp.app.common.ProjectConstants;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigInteger;
import com.erp.app.catalog.catalogSecurity.criteria.CatalogSecurityHdrSrchCriteria;

/**
* <p>A <strong>Common Dao</strong> provides database access functions
*  for delegate classes.
*
* @author          Saint Peng 
* @version 1.0     2007/03/29
* @since           FrameWork 1.1
*
*/
public class CatalogSecurityCommonDao {

	private CatalogSecurityCommonUtil util = new CatalogSecurityCommonUtil();

	/**
	* Returns the SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Saint Peng 
	* @since      Framework 1.1
	*/
	public static String getHdrSqlQuery(SimpleBaseSrchCriteria obj) {

		CatalogSecurityHdrSrchCriteria criteria = (CatalogSecurityHdrSrchCriteria) obj;
		String sql = "";
		String whereClause = hdrToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = " ORDER BY A.COMPANY_CODE, A.USER_CODE DESC ";

		if(!criteria.isSearchResult()){
			
		} else {
			sql =  "SELECT A.COMPANY_CODE,"+
			 "        A.USER_CODE,"+
			 "        A.USER_NAME,"+
			 "        A.ROLE_GROUP_ID"+
			 " FROM   T_USER A "+
			  whereClause
			 + groupByClause
			 + orderByClause;
		}

		return sql;
	}

	/**
	* Sets the values of a result set to a vo for a search screen
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Saint Peng 
	* @since      Framework 1.1
	*/
	public static SimpleBaseVo getHdrSearchResultSetValues(ResultSet rs) throws SQLException {


		CatalogSecurityHdrVo vo = new CatalogSecurityHdrVo();

		vo.setCompanyCode(rs.getString("COMPANY_CODE"));
		vo.setUserCode(rs.getString("USER_CODE"));
		vo.setHdrUserName(rs.getString("USER_NAME"));
		vo.setHdrRoleGroupId(rs.getString("ROLE_GROUP_ID"));
		return vo;
	}

	/**
	* Returns the where clause of a SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing the where clause
	*
	* @author     Saint Peng 
	* @since      Framework 1.1
	*/
	public static String hdrToWhereClause(CatalogSecurityHdrSrchCriteria crit) {

		CatalogSecurityHdrSrchCriteria criteria = (CatalogSecurityHdrSrchCriteria) crit;
		StringBuffer whereClause = new StringBuffer();

		if (criteria.getSrchCompanyCode() != null) {
			whereClause.append( " where ");
			whereClause.append(" A.COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase()+"' ");
		}
		
		
		if (criteria.getSrchHdrCatalogUserIdFr() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" upper(" + "A.USER_CODE" + ") >= '" + criteria.getSrchHdrCatalogUserIdFr().trim().toUpperCase()+"' ");
        }

        if (criteria.getSrchHdrCatalogUserIdTo() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" upper(" + "A.USER_CODE"  + ") <= '" + criteria.getSrchHdrCatalogUserIdTo().trim().toUpperCase()+"' ");
        }
		
		
		
		if (criteria.getSrchRoleGroupIdFr() != null) {
			whereClause.append( "AND ");
			whereClause.append(" UPPER(A.ROLE_GROUP_ID) >= '" + criteria.getSrchRoleGroupIdFr().trim().toUpperCase() +"' ");
		}
		
		if (criteria.getSrchRoleGroupIdTo() != null) {
			whereClause.append( "AND ");
			whereClause.append(" UPPER(A.ROLE_GROUP_ID) >= '" + criteria.getSrchRoleGroupIdTo().trim().toUpperCase() +"' ");
		}
		
		if (criteria.getSrchHdrUserNameFr() != null) {
			whereClause.append( "AND ");
			whereClause.append(" upper(A.USER_NAME) like '%" + criteria.getSrchHdrUserNameFr().trim().toUpperCase() +"%' ");
		}

		

		return whereClause.toString();
	}

}
